﻿CREATE TRIGGER [tr_PurchaseBatch]
ON [dbo].[t_PurchaseBatch_Loans]
FOR INSERT,UPDATE
AS

IF @@ROWCOUNT = 0 RETURN

IF NOT EXISTS(
		SELECT * FROM t_PurchaseBatch AS PB
		INNER JOIN inserted/*t_PurchaseBatch_Loans*/AS PB_L ON PB.ID = PB_L.BatchID
		LEFT OUTER JOIN	t_Loan AS L ON PB_L.LoanID = L.ID AND PB.ContractHolderID = L.ContractHolderID
		WHERE (L.ID IS NULL)
	)RETURN

ROLLBACK TRAN
RAISERROR('All loans in batch must belong to same Contract Holder.',16,1)
